package com.xjtu.platform.reader.dao;

import com.xjtu.platform.admin.dto.bjyDto.AddRuleDto;
import com.xjtu.platform.admin.entity.PenaltyRule;
import com.xjtu.platform.common.entity.FileEntity;
import com.xjtu.platform.common.entity.PrimaryKey;
import com.xjtu.platform.reader.dto.BorrowBookDto;
import com.xjtu.platform.reader.dto.BorrowHisDto;
import com.xjtu.platform.reader.dto.GetBookListParamDto;
import com.xjtu.platform.reader.entity.*;
import org.apache.ibatis.annotations.*;

import java.sql.Timestamp;
import java.util.List;

@Mapper
public interface ReaderDao {

    @Select("select * from book")
    List<BookBaseInfo> getBookBaseInfo();

    @Select("select file.file_id,file_name,file_address as file_url from file,book_pic " +
            "where book_pic.file_id = file.file_id " +
            "and book_pic.book_id = #{bookId} " +
            "order by file.file_id ASC")
    List<FileEntity> getBookPic(String bookId);


    @Select("select author.author_id,author_name,author_info " +
            "from author,author_book " +
            "where author_book.author_id = author.author_id " +
            "and author_book.book_id = #{bookId}")
    List<Author> getBookAuthors(String bookId);

    @Select("select library_label.label_id, label_name " +
            "from library_label,book_lable " +
            "where book_lable.label_id = library_label.label_id " +
            "and book_lable.book_id = #{bookId}")
    List<BookLabel> getBookLabelList(String bookId);

//    @SelectKey(statement="SELECT LAST_INSERT_ID() AS id", keyProperty="pk", before=false, resultType=int.class)
    @Insert("insert into reader_book_borrow_his " +
            "VALUES(#{dto.book_id},#{id},#{pk.id},#{time},#{dto.borrow_time},null,'0',#{dto.book_num})")
    @Options(useGeneratedKeys = true, keyColumn = "book_reader_his_id" , keyProperty = "pk.id")
    void borrowNewBook(@Param("dto") BorrowBookDto borrowBookDto, @Param("id") String readerId, @Param("time") Timestamp startTime,@Param("pk") PrimaryKey primaryKey);

    @Select("select * from reader_book_borrow_his " +
            "where reader_id = #{dto.reader_id} and borrow_state like #{dto.borrow_state}" +
            "and book_id in (select book_id from book where book_name like #{dto.book_name} and ISBN like #{dto.isbn} and book_id in (SELECT book_id from author_book,author where author_name like #{dto.author_name} and author.author_id = author_book.author_id) and " +
            "book_id in (SELECT book_id from book_class,class where class_name like #{dto.class_name} and class.class_id = book_class.class_id) and " +
            "book_id in (SELECT book_id from book_lable,library_label where label_name like #{dto.label_name} and book_lable.label_id = library_label.label_id) ) " +
            "ORDER BY start_time ASC ")
    List<BorrowHisBaseInfo> getBorrowHisBaseInfo(@Param("dto") BorrowHisDto dto);

    @Select("select * from book where book_id = #{bookId}")
    BookBaseInfo getBookBaseInfoById(String bookId);

    @Update("update book set current_num = cast(current_num as unsigned) - cast(#{book_num} as unsigned) where book_id = #{book_id}")
    void updateBookTotalNum(BorrowBookDto borrowBookDto);

    @Select("select * from book " +
            "where book_name like #{dto.book_name} " +
            "and ISBN like #{dto.isbn} and " +
            "book_id in (SELECT book_id from author_book,author where author_name like #{dto.author_name} and author.author_id = author_book.author_id) and \n" +
            "book_id in (SELECT book_id from book_class,class where class_name like #{dto.class_name} and class.class_id = book_class.class_id) and \n" +
            "book_id in (SELECT book_id from book_lable,library_label where label_name like #{dto.label_name} and book_lable.label_id = library_label.label_id) " +
            "order by book_id ASC")
    List<BookBaseInfo> getBookBaseInfoByParam(@Param("dto") GetBookListParamDto dto);

    @Select("select class_name from book_class,class where book_class.class_id = class.class_id and book_id = #{bookId}")
    String getBookClass(String bookId);

    @Select("select money,time from user_penalty where book_reader_his_id = #{bookReaderHisId} limit 1")
    PenaltyEntity getPenaltyById(String bookReaderHisId);

    @Select("select * from penalty order by cast(day as SIGNED) ASC")
    List<PenaltyRule> getPenaltyRule();

    @Insert("insert into penalty values(null,#{d.penaltyName},#{d.penaltyMoney},#{d.day})")
    void addRule(@Param("d") AddRuleDto addRuleDto);

    @Delete("delete from penalty where penalty_id = #{id}")
    void deleteRole(String id);
}
